--POUR CONTROLE

define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_cli_cliadr AS (SELECT * FROM &erp.cli_cliadr WHERE 1=2);


/*SELECT * FROM (
SELECT &outils.stragg(source) source,client_cod, cliadr_cod, piece_no, rep_cod, camion_cod, tvaregime_cod, adresse_id, clientref_cod, cliadrref_cod, pieceref_no, sectcial_cod, enreg_id, exped_cod, prfcompta_cod, enreg_s, aff_nom, cliadr_typ, implicite_ok, except_ok, exped_jour, jlivlu_ok, jlivma_ok, jlivme_ok, jlivje_ok, jlivve_ok, jlivsa_ok, ouv1_heur, ferm1_heur, ouv2_heur, ferm2_heur, envoidoc_lib, envoidoc_nb, mqimplicite_lib, libre1_val, libre2_val, libre3_val, libre4_val, libre5_val
from (
  SELECT 'uv' source,a.* FROM se_cli_cliadr a
  UNION ALL
  SELECT 'se' source,a.* FROM &erp.cli_cliadr a --where cliadr_cod='000'
) group BY client_cod, cliadr_cod, piece_no, rep_cod, camion_cod, tvaregime_cod, adresse_id, clientref_cod, cliadrref_cod, pieceref_no, sectcial_cod, enreg_id, exped_cod, prfcompta_cod, enreg_s, aff_nom, cliadr_typ, implicite_ok, except_ok, exped_jour, jlivlu_ok, jlivma_ok, jlivme_ok, jlivje_ok, jlivve_ok, jlivsa_ok, ouv1_heur, ferm1_heur, ouv2_heur, ferm2_heur, envoidoc_lib, envoidoc_nb, mqimplicite_lib, libre1_val, libre2_val, libre3_val, libre4_val, libre5_val
HAVING Count(1) !=2   )
ORDER BY client_cod */


DELETE FROM  se_cli_cliadr;


INSERT INTO se_cli_cliadr
 (SELECT id client_cod,
         '000' cliadr_cod,
         0 piece_no,
         NULL rep_cod,
         NULL camion_cod,
         NULL tvaregime_cod,
         0 adresse_id,
         NULL clientref_cod,
         NULL cliadrref_cod,
         0 pieceref_no,
         NULL sectcial_cod,
         0 enreg_id,
         NULL exped_cod,
         NULL prfcompta_cod,
         'N' enreg_s,
         trim(SubStr(NOM || Nvl2(cp,' ' || cp,'') || Nvl2(bd,' ' || bd,'') || Nvl2(adresse1,' ' || adresse1,'') || Nvl2(compl_adresse,' ' || compl_adresse,''),1,80)) aff_nom,
         '00' cliadr_typ,
         'N'implicite_ok,
         'N' except_ok,
         NULL exped_jour,
         'O' jlivlu_ok,
         'O' jlivma_ok,
         'O' jlivme_ok,
         'O' jlivje_ok,
         'O' jlivve_ok,
         'N' jlivsa_ok,
         NULL ouv1_heur,
         NULL ferm1_heur,
         NULL ouv2_heur,
         NULL ferm2_heur,
         NULL envoidoc_lib,
         NULL envoidoc_nb,
         NULL mqimplicite_lib,
         NULL libre1_val,
         NULL libre2_val,
         NULL libre3_val,
         NULL libre4_val,
         NULL libre5_val
    FROM uv_cclients c
) ;

INSERT INTO se_cli_cliadr
 (SELECT SubStr(a.id,1,5) client_cod,
         SubStr(a.id,6,3) cliadr_cod,
         0 piece_no,
         NOM_REPR rep_cod,
         NULL camion_cod,
         NULL tvaregime_cod,
         0 adresse_id,
         NULL clientref_cod,
         NULL cliadrref_cod,
         0 pieceref_no,
         NULL sectcial_cod,
         0 enreg_id,
         MODEXP exped_cod,
         NULL prfcompta_cod,
         'N' enreg_s,
         trim(SubStr(a.NOM || Nvl2(a.cp,' ' || a.cp,'') || Nvl2(a.bd,' ' || a.bd,'') || Nvl2(a.adresse1,' ' || a.adresse1,'') || Nvl2(a.adresse2,' ' || a.adresse2,''),1,80)) aff_nom,
         Decode(TYPE,'L','LI','M','OR','00') cliadr_typ,
         'N'implicite_ok,
         'N' except_ok,
         NULL exped_jour,
         'O' jlivlu_ok,
         'O' jlivma_ok,
         'O' jlivme_ok,
         'O' jlivje_ok,
         'O' jlivve_ok,
         'N' jlivsa_ok,
         NULL ouv1_heur,
         NULL ferm1_heur,
         NULL ouv2_heur,
         NULL ferm2_heur,
         NULL envoidoc_lib,
         NULL envoidoc_nb,
         NULL mqimplicite_lib,
         NULL libre1_val,
         NULL libre2_val,
         NULL libre3_val,
         NULL libre4_val,
         NULL libre5_val
   FROM UV_CADRESSES a,uv_cclients c
   WHERE SubStr(a.id,1,5)=c.id
) ;
     -- SELECT * FROM UV_CADRESSES

/*maj enreg_id*/
 UPDATE se_cli_cliadr tf
 SET enreg_id = (select enreg_id FROM &erp.cli_cliadr f WHERE f.client_cod=tf.client_cod AND f.cliadr_cod=tf.cliadr_cod)
 WHERE EXISTS (SELECT 1 FROM &erp.cli_cliadr f WHERE f.client_cod=tf.client_cod AND f.cliadr_cod=tf.cliadr_cod);

 UPDATE se_cli_cliadr SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;


/*maj adresse_id*/
 UPDATE se_cli_cliadr tf
 SET adresse_id = (select adresse_id FROM &erp.cli_cliadr f WHERE f.client_cod=tf.client_cod AND f.cliadr_cod=tf.cliadr_cod)
 WHERE EXISTS (SELECT 1 FROM &erp.cli_cliadr f WHERE f.client_cod=tf.client_cod AND f.cliadr_cod=tf.cliadr_cod);

 UPDATE se_cli_cliadr SET adresse_id=&outils.gen_adresse.NEXTVAL WHERE adresse_id IS NULL OR adresse_id =0;



COMMIT;